﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace IBS
{
    public partial class IbsDataContext
    {
        public IEnumerable<Site> GetSites(DateTime lastSyncDate)
        {
            var sql = @"select GUID_Record, siteibscode, sitename, rccode,Address, dateupdate  from tbsite
                        where 1 = 1                            
                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                            AND COMPANY	= 2	
                        order by dateupdate";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<Site>(sql);
        }      

        public IEnumerable<Supplier> GetSuppliers(DateTime lastSyncDate)
        {
            var sql = @"select 
                            SUPIBSCODE, SUPNAME, CHDATE from tbsupplier
                        where 1 = 1          
                            AND  SUPPLIER_TYPE = 1	  
                             AND SUPIBSCODE >= 1000             
                            AND SUPIBSCODE < 5000
                            AND CHDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                        order by CHDATE";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<Supplier>(sql);
        }


        public IEnumerable<Item> GetItems(DateTime lastSyncDate)
        {
            var sql = @"select 
                            ITEMCODE, SUPIBSCODE, ITEMSTATCODE, LONGNAME, SHORTNAME, CHDATE 
                        from 
                            TBITEM 
                        where 1 = 1
                            AND ITEMSTATCODE IN (1, 2)
                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                             AND SUPIBSCODE >= 1000             
                            AND SUPIBSCODE < 5000
                        order by CHDATE";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<Item>(sql);
        }

        public DateTime GetLastSyncDate(string paramName)
        {
            using (var connection = new OracleConnection(this.connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    var sql = @"select Value from edex.TBSETTING where Name = '{0}'";
                    sql = String.Format(sql, paramName);
                    command.CommandText = sql;
                    using (var reader = command.ExecuteReader())
                    {
                        reader.Read();
                        return DateTime.Parse(reader.GetString(0));
                    }
                }
            }
        }

        public void SetLastSyncDate(String LastSyncDate, string paramName)
        {
            using (var connection = new OracleConnection(this.connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    var sql = @"update edex.TBSETTING set value = '{0}' where name = '{1}'";
                    sql = String.Format(sql, LastSyncDate, paramName);
                    command.CommandText = sql;
                    command.ExecuteReader();
                }
            }
        }

    }
}
